CREATE proc [dbo].[TeamCapacityTotal] @ProjectNodeGUID nvarchar(255),@IterationSK int
as begin
SELECT iter.IterationPath, Team.TeamFieldValue as Team,
(Sum(TC.Capacity)*(SELECT(DATEDIFF(dd, StartDate, FinishDate) + 1)-(DATEDIFF(wk, StartDate, FinishDate) * 2)-(CASE WHEN DATENAME(dw, StartDate) = 'Sunday' THEN 1 ELSE 0 END)-(CASE WHEN DATENAME(dw, FinishDate) = 'Saturday' THEN 1 ELSE 0 END))) as 'Total Capacity',
convert(date,StartDate) as 'IterationStart (YYYY-MM-DD)',convert(date,FinishDate) as 'IterationEnd (YYYY-MM-DD)',
(SELECT(DATEDIFF(dd, StartDate, FinishDate) + 1)-(DATEDIFF(wk, StartDate, FinishDate) * 2)-(CASE WHEN DATENAME(dw, StartDate) = 'Sunday' THEN 1 ELSE 0 END)-(CASE WHEN DATENAME(dw, FinishDate) = 'Saturday' THEN 1 ELSE 0 END)) as 'Total Working Days'
  FROM tbl_TeamConfigurationCapacity Tc inner join 
Constants Con on TC.TeamMemberId=con.TeamFoundationId  inner join
tbl_TeamConfigurationTeamFields Team on Tc.TeamId=Team.TeamId inner join
  [Tfs_Warehouse].[dbo].[DimIteration] iter on TC.IterationId=iter.IterationGUID
 where iter.ProjectGUID!='NULL' and iter.ProjectGUID=@ProjectNodeGUID  and iter.IterationSK = @IterationSK
  group by iter.IterationPath,Team.TeamFieldValue,StartDate,FinishDate

   end


